package d1030;

import d1026.DBHelper;
import org.junit.Assert;
import org.junit.Test;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Map;
 @SuppressWarnings({"all"})
public class DBHelperTest {
    @Test
    public void test1() throws SQLException{

        String sql = "select sysdate now from dual";

        Map<String,Object> map = DBHelper.selectOneMap(sql);

        System.out.println(map);
        System.out.println(map.get("NOW").getClass());
        //断言该map不为空
        Assert.assertNotNull(map);
        //断言该map中包含一个名字为NOW的日期字段
        Assert.assertTrue(map.get("NOW") instanceof java.util.Date);
    }
          @Test
    //先写测试，再写程序==>测试驱动开发==> 极限编程
    public void test2() throws SQLException{
        String sql = "select*from emp where empno >? and mgr >?";
        DBHelper.Page<Map<String,Object>> page = DBHelper.selectPage(sql,1,5,1000,2000);
        //断言页数是1
        Assert.assertEquals(1,page.getPage());
        //断言每页行数页数是5
        Assert.assertEquals(1,page.getPage());
        //断言当前页数据行数
        Assert.assertEquals(5,page.getData());

        page = DBHelper.selectPage(sql,3,5,1000,2000);
        //断言页数是3
        Assert.assertEquals(3,page.getPage());
        //断言每页行页数是5
        Assert.assertEquals(5,page.getSize());
        //断言当前页数据行数，只要4行
        Assert.assertEquals(3,page.getData().size());
    }
    @Test
    public void test3() throws SQLException{
        //初始化emp 和dept 的测试数据
        DBHelper.update("delete dept where deptno = ?",43);
        DBHelper.update("delete emp where deptno =? or deptno is null",43);

        //新增一个部门，同时添加该部门的一个经理的员工，员工姓名叫"待入职"
        DBHelper.execute(connection -> {
            try{
                PreparedStatement ps1 = connection.prepareStatement("insert into dept values(?,?,?,)");
                PreparedStatement ps2 = connection.prepareStatement("insert into emp values(?,?,?,)");
                ps1.setInt(1,43);
                ps1.setString(2,"xxx部门");
                ps1.setString(3,"xxx市");
                ps1.executeUpdate();
                ps2.setInt(1,8000);

                ps2.setString(2,"待入职");
                ps2.setObject(3,null);
                ps2.setObject(4,null);
                ps2.setObject(5,null);
                ps2.setObject(6,null);
                ps2.setObject(7,null);
                ps2.setObject(8,43);
                ps2.executeUpdate();

            }catch (SQLException e){
                throw new RuntimeException("新增部门失败",e);
            }
            return null;
        });

        String testSql1 = "select*from dept where deptno=?";
        Map<String,Object> dept = DBHelper.selectOneMap(testSql1,43);
        //由于执行过程出现异常，那么当前查询dept==null
        Assert.assertNull(dept);

        DBHelper.execute(connection -> {
            try{
                PreparedStatement ps1 = connection.prepareStatement("insert into dept values(?,?,?,)");
                PreparedStatement ps2 = connection.prepareStatement("insert into emp values(?,?,?,)");
                ps1.setInt(1,43);
                ps1.setString(2,"xxx部门");
                ps1.setString(3,"xxx市");
                ps1.executeUpdate();
                ps2.setInt(1,8000);

                ps2.setString(2,"待入职");
                ps2.setObject(3,null);
                ps2.setObject(4,null);
                ps2.setObject(5,null);
                ps2.setObject(6,null);
                ps2.setObject(7,null);
                ps2.setObject(8,43);
                ps2.executeUpdate();

            }catch (SQLException e){
                throw new RuntimeException("新增部门失败",e);
            }
            return null;
        });

        dept = DBHelper.selectOneMap(testSql1,43);
        Assert.assertNull(dept);

        //验证新员工
        String testSql2 = "select*from emp where deptno=?";
        Map<String,Object> emp = DBHelper.selectOneMap(testSql2,43);
        Assert.assertNotNull(emp);
        Assert.assertEquals("待入职",emp.get("ENAME"));
    }
}
